﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// Fwmmszbt数据类
    /// </summary>
    public class Fwmmszbt
    {

        /// <summary>
        /// 得到列表
        /// </summary>
        /// <param name="where">where条件</param>
        /// <returns>数据列表</returns>
        public DataSet GetAllList(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [nc].nc_fwmmszbt ");
            strSql.Append(" where " + where);
            strSql.Append(" order by id asc ");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            return ds;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataSet GetChangeData(string where) 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(distinct convert(char(10),btrq,112)) as cou,convert(char(10),max(btrq),112) day  ");
            strSql.Append(" from dbo.nc_fwmmszbt where " + where);
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            return ds;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataSet GetData(string day)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select convert(char(10),dateadd(mm,1,'" + day + "'),112)  as day ");
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            return ds;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">model对象</param>
        /// <returns>增加的个数</returns>
        public decimal Add(ElingOA6_Admin_DSJCJ.Model.Fwmmszbt model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [nc].nc_fwmmszbt(");
            strSql.Append("bh,nsr,sfz,yjsk,btbl,ybtse,khyh,zh,btrq,sz)");
            strSql.Append(" values (");
            strSql.Append("@bh,@nsr,@sfz,@yjsk,@btbl,@ybtse,@khyh,@zh,@btrq,@sz)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@bh", SqlDbType.Int,4),
					new SqlParameter("@nsr", SqlDbType.VarChar,50),
					new SqlParameter("@sfz", SqlDbType.VarChar,18),
					new SqlParameter("@yjsk", SqlDbType.Decimal,9),
					new SqlParameter("@btbl", SqlDbType.Decimal,9),
					new SqlParameter("@ybtse", SqlDbType.Decimal,9),
					new SqlParameter("@khyh", SqlDbType.VarChar,10),
					new SqlParameter("@zh", SqlDbType.VarChar,50),
					new SqlParameter("@btrq", SqlDbType.DateTime),
					new SqlParameter("@sz", SqlDbType.VarChar,20)};
            parameters[0].Value = model.bh;
            parameters[1].Value = model.nsr;
            parameters[2].Value = model.sfz;
            parameters[3].Value = model.yjsk;
            parameters[4].Value = model.btbl;
            parameters[5].Value = model.ybtse;
            parameters[6].Value = model.khyh;
            parameters[7].Value = model.zh;
            parameters[8].Value = model.btrq;
            parameters[9].Value = model.sz;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToDecimal(obj);
            }
        }

        /// <summary>
        /// 批量删除
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
        /// </summary>
        /// <param name="idArray">数组编号</param>
        public void DeleteMany(string[] idArray)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            if (idArray.Length > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("delete from [nc].nc_fwmmszbt ");
                strSql.Append(" where id=@id ");

                string strSqlFw = strSql.ToString();

                foreach (string idNo in idArray)
                {
                    cmds = new CommandInfo();

                    SqlParameter[] parameters = {
					    new SqlParameter("@id", SqlDbType.Decimal)};

                    parameters[0].Value = idNo;

                    cmds.CommandText = strSqlFw;
                    cmds.Parameters = parameters;
                    cmdList.Add(cmds);
                }

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);

        }

        /// <summary>
        /// 是否存在该记录
        /// <creator>chenly</creator>
        /// <date>2013-7-15</date>
        /// </summary>      
        /// <param name="sfz">身份证不能重复</param>
        /// <returns>是否存在</returns>
        public bool Exists(string sfz)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [nc].nc_fwmmszbt");
            strSql.Append(" where sfz=@sfz ");
            SqlParameter[] parameters = {
					new SqlParameter("@sfz", SqlDbType.NVarChar,50)};
            parameters[0].Value = sfz;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
       /// <param name="model">model对象</param>
       /// <returns>是否更新</returns>
        public bool Update(ElingOA6_Admin_DSJCJ.Model.Fwmmszbt model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [nc].nc_fwmmszbt set ");
            strSql.Append("bh=@bh,");
            strSql.Append("nsr=@nsr,");
            strSql.Append("sfz=@sfz,");
            strSql.Append("yjsk=@yjsk,");
            strSql.Append("btbl=@btbl,");
            strSql.Append("ybtse=@ybtse,");
            strSql.Append("khyh=@khyh,");
            strSql.Append("zh=@zh,");
            strSql.Append("btrq=@btrq,");
            strSql.Append("sz=@sz");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@bh", SqlDbType.Int,4),
					new SqlParameter("@nsr", SqlDbType.VarChar,50),
					new SqlParameter("@sfz", SqlDbType.VarChar,18),
					new SqlParameter("@yjsk", SqlDbType.Decimal,9),
					new SqlParameter("@btbl", SqlDbType.Decimal,9),
					new SqlParameter("@ybtse", SqlDbType.Decimal,9),
					new SqlParameter("@khyh", SqlDbType.VarChar,10),
					new SqlParameter("@zh", SqlDbType.VarChar,50),
					new SqlParameter("@btrq", SqlDbType.DateTime),
					new SqlParameter("@sz", SqlDbType.VarChar,20),
					new SqlParameter("@id", SqlDbType.Decimal)};
            parameters[0].Value = model.bh;
            parameters[1].Value = model.nsr;
            parameters[2].Value = model.sfz;
            parameters[3].Value = model.yjsk;
            parameters[4].Value = model.btbl;
            parameters[5].Value = model.ybtse;
            parameters[6].Value = model.khyh;
            parameters[7].Value = model.zh;
            parameters[8].Value = model.btrq;
            parameters[9].Value = model.sz;
            parameters[10].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>是否删除</returns>
        public bool Delete(decimal id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [nc].nc_fwmmszbt ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Decimal)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <creator>chenly</creator>
        /// <date>2013-8-1</date>
        /// <param name="id">主键</param>
        /// <returns>实体对象</returns>
        public Model.Fwmmszbt GetModel(decimal id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,bh,nsr,sfz,yjsk,btbl,ybtse,khyh,zh,btrq,sz from [nc].nc_fwmmszbt ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Decimal)};
            parameters[0].Value = id;

            Model.Fwmmszbt model = new Model.Fwmmszbt();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = decimal.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["bh"] != null && ds.Tables[0].Rows[0]["bh"].ToString() != "")
                {
                    model.bh = int.Parse(ds.Tables[0].Rows[0]["bh"].ToString());
                }
                if (ds.Tables[0].Rows[0]["nsr"] != null && ds.Tables[0].Rows[0]["nsr"].ToString() != "")
                {
                    model.nsr = ds.Tables[0].Rows[0]["nsr"].ToString();
                }
                if (ds.Tables[0].Rows[0]["sfz"] != null && ds.Tables[0].Rows[0]["sfz"].ToString() != "")
                {
                    model.sfz = ds.Tables[0].Rows[0]["sfz"].ToString();
                }
                if (ds.Tables[0].Rows[0]["yjsk"] != null && ds.Tables[0].Rows[0]["yjsk"].ToString() != "")
                {
                    model.yjsk = decimal.Parse(ds.Tables[0].Rows[0]["yjsk"].ToString());
                }
                if (ds.Tables[0].Rows[0]["btbl"] != null && ds.Tables[0].Rows[0]["btbl"].ToString() != "")
                {
                    model.btbl = decimal.Parse(ds.Tables[0].Rows[0]["btbl"].ToString());
                }

                if (ds.Tables[0].Rows[0]["ybtse"] != null && ds.Tables[0].Rows[0]["ybtse"].ToString() != "")
                {
                    model.ybtse = decimal.Parse(ds.Tables[0].Rows[0]["ybtse"].ToString());
                }

                if (ds.Tables[0].Rows[0]["khyh"] != null && ds.Tables[0].Rows[0]["khyh"].ToString() != "")
                {
                    model.khyh = ds.Tables[0].Rows[0]["khyh"].ToString();
                }

                if (ds.Tables[0].Rows[0]["zh"] != null && ds.Tables[0].Rows[0]["zh"].ToString() != "")
                {
                    model.zh = ds.Tables[0].Rows[0]["zh"].ToString();
                }
                if (ds.Tables[0].Rows[0]["btrq"] != null && ds.Tables[0].Rows[0]["btrq"].ToString() != "")
                {
                    model.btrq = DateTime.Parse(ds.Tables[0].Rows[0]["btrq"].ToString());
                }
                if (ds.Tables[0].Rows[0]["sz"] != null && ds.Tables[0].Rows[0]["sz"].ToString() != "")
                {
                    model.sz = ds.Tables[0].Rows[0]["sz"].ToString();
                }               
                return model;
            }
            else
            {
                return null;
            }
        }


    }
}
